/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import DTO.DTOUser;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

/**
 *
 * @author Pham
 */
public class DALUser {
    ConnectionDB conn;
    public DALUser(){
        conn = new ConnectionDB();
    }
    
    //method check login
    public DTOUser checkLogin(String username, String password)
    {
        try{
            DTOUser dtoUser = null;
            String querySelect = "SELECT * FROM `user` WHERE username = ? AND password = ? AND user_role = 'admin'";
            PreparedStatement stmt = conn.getConnectionDB().prepareStatement(querySelect);
            stmt.setString(1, username);
            stmt.setString(2, conn.getMD5(password));
            ResultSet rs = stmt.executeQuery();
            while(rs.next())
            {
                dtoUser = new DTOUser(rs);
            }
            return dtoUser;
        }
        catch(SQLException ex){
            System.err.printf(ex.getMessage());
            return null;
        }
    }

    //method change password
    public boolean changePassword(String username, String oldPass, String newPass) {
        try{
            DTOUser dtoUser = checkLogin(username, oldPass);
            if(dtoUser == null){
                return false;
            }
            else
            {
                String queryUpdate = "update `user` set password = ? where username = ?";
                PreparedStatement pstmt = conn.getConnectionDB().prepareStatement(queryUpdate);
                pstmt.setString(1, conn.getMD5(newPass));
                pstmt.setString(2, username);
                return pstmt.executeUpdate() == 1;
            }
        }
        catch(SQLException ex){
            System.err.printf(ex.getMessage());
            return false;
        }
        
    }
    
    //get information of all user
    public ArrayList<DTOUser> getAllUser()
    {
        try{
            ArrayList<DTOUser> list = new ArrayList<>();
            String querySelect = "SELECT * FROM `user`";
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                list.add((new DTOUser(rs)));
            }
            return list;
        }
        catch(SQLException ex)
        {
            System.err.print(ex.getMessage());
            return null;
        }
    }
    
    //method create new user
    public boolean createNewUser(DTOUser dtoUser)
    {
        try{
            String queryInsert = "INSERT INTO `user`(username, password, user_role, user_fullname, user_address, user_email, user_phone, "
                    + "user_gender, user_datecreate, user_actived) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String date = dateformat.format(dtoUser.getUserDateCreated());
            PreparedStatement pstmt = conn.getConnectionDB().prepareStatement(queryInsert);
            pstmt.setString(1, dtoUser.getUserName());
            pstmt.setString(2, conn.getMD5(dtoUser.getPassword()));
            pstmt.setString(3, dtoUser.getRole());
            pstmt.setString(4, dtoUser.getUserFullName());
            pstmt.setString(5, dtoUser.getUserAddress());
            pstmt.setString(6, dtoUser.getUserEmail());
            pstmt.setString(7, dtoUser.getUserPhone());
            pstmt.setString(8, dtoUser.getUserGender());
            pstmt.setString(9, date);
            pstmt.setInt(10, dtoUser.getUserActived());
            return pstmt.executeUpdate() == 1;
        }
        catch(SQLException ex)
        {
            System.err.print(ex.getMessage());
            return false;
        }
    }

    //method update user
    public boolean updateUser(DTOUser dtoUser)
    {
        try{
            String queryUpdate = "UPDATE `user` SET username = ?, password = ?, user_role = ?, user_fullname = ?, user_address = ?, "
                    + "user_email = ?, user_phone = ?, user_gender = ? WHERE user_id = ?";
            PreparedStatement pstmt = conn.getConnectionDB().prepareStatement(queryUpdate);
            pstmt.setString(1, dtoUser.getUserName());
            pstmt.setString(2, conn.getMD5(dtoUser.getPassword()));
            pstmt.setString(3, dtoUser.getRole());
            pstmt.setString(4, dtoUser.getUserFullName());
            pstmt.setString(5, dtoUser.getUserAddress());
            pstmt.setString(6, dtoUser.getUserEmail());
            pstmt.setString(7, dtoUser.getUserPhone());
            pstmt.setString(8, dtoUser.getUserGender());
            pstmt.setInt(9, dtoUser.getUserId());
            return pstmt.executeUpdate() == 1;
        }
        catch(SQLException ex)
        {
            System.err.print(ex.getMessage());
            return false;
        }
    }
    
    //method get user by id
    public DTOUser getUserbyId(int id)
    {
        try{
            DTOUser dtoUser = null;
            String querySelect = "SELECT * FROM `user` WHERE user_id = " + id;
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                dtoUser = new DTOUser(rs);
            }
            return dtoUser;
        }
        catch(Exception ex)
        {
            System.err.print(ex.getMessage());
            return null;
        }
    }
    
    //method delete user
    public boolean deleteUser(int id)
    {
        try{
            String queryDel = "DELETE FROM `user` WHERE user_id = " + id;
            return conn.exeUpdate(queryDel);
        }
        catch(Exception ex)
        {
            System.err.print(ex.getMessage());
            return false;
        }
    }
}
